package fy.erp.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.LogFactory;
import org.json.JSONArray;


import fy.config.FyConfig;
import fy.erp.bean.ProjectCostBean;
import fy.util.DBUtil;
import fy.util.MyUtil;

public final class QueryProjectCostDao {

	private static org.apache.commons.logging.Log log = LogFactory
			.getLog(QueryProjectCostDao.class);

	private final static String[][] companyArray = { { "福耀玻璃", "01" },
			{ "万达公司", "02" }, { "长春轿车", "04" }, { "重庆配件公司", "05" },
			{ "上海轿车", "06" }, { "机械制造", "07" }, { "北京公司", "11" },
			{ "通辽公司", "12" }, { "长春巴士", "13" }, { "重庆公司（新区）", "14" },
			{ "广州福耀", "18" }, { "包边公司", "19" }, { "集团总办", "20" },
			{ "大巴公司", "26" }, { "上海客车", "27" }, { "湖北公司", "30" },
			{ "郑州福耀", "40" }, { "广东佛山", "42" }, { "成都绿榕", "45" },
			{ "沈阳福耀", "43" }, { "烟台福耀", "46" }, { "福州福耀模具", "47" } };

	private static Map<String, String> companyMap;

	private static final long ZONGBAN_ACCOUNT_ID = 5686590792835548528L;

	private String early;

	private String last;

	private String companyName;

	private String number;

	private String error;

	private long accountID;

	private boolean expend;

	private List<ProjectCostBean> projectCostBeans;

	private Map<String, ProjectCostBean> costMap = new HashMap<String, ProjectCostBean>();

	private Map<String, List<ProjectCostBean>> costMapKeyCompany = new HashMap<String, List<ProjectCostBean>>();

	private List<String> params = new ArrayList<String>();

	private JSONArray costBeans = new JSONArray();

	private void addProjectCostBean(ProjectCostBean bean) {
		List<ProjectCostBean> beans = costMapKeyCompany.get(bean.getCompany());
		if (beans == null) {
			beans = new ArrayList<ProjectCostBean>();
			costMapKeyCompany.put(bean.getCompany(), beans);
		}
		beans.add(bean);
	}

	public void setAccountID(long accountID) {
		this.accountID = accountID;
	}

	public void setExpend(boolean expend) {
		this.expend = expend;
		projectCostBeans = new ArrayList<ProjectCostBean>();
	}

	public List<ProjectCostBean> getProjectCostBeans() {
		return projectCostBeans;
	}

	public String getResult() {
		return costBeans.toString();
	}

	public void setEarly(String early) {
		this.early = "".equals(early) ? "2009-01-01" : early.substring(0,
				early.indexOf("T"));
	}

	public void setLast(String last) {
		this.last = "".equals(last) ? "3000-01-01" : last.substring(0,
				last.indexOf("T"));
		;
	}

	public String getError() {
		return error;
	}

	public void setCompanyName(String companyName) {
		this.companyName = companyName;
	}

	public void setNumber(String number) {
		this.number = number;
	}

	/**
	 * 查询项目花费
	 * 
	 * @return
	 */
	public boolean query() {
		if (!queryProjectInfo()) {
			error = "系统查询立项项目数据异常";
		} else {
			return true;
		}
		return false;
	}

	/**
	 * 查询存储在OA系统中的项目信息，项目名称，项目令号，科目段，预计费用
	 * 
	 * @return
	 */
	private boolean queryProjectInfo() {
		boolean success = false;

		if (companyMap == null) {
			companyMap = new Hashtable<String, String>();
			for (int i = 0, length = companyArray.length; i < length; i++) {
				companyMap.put(companyArray[i][0], companyArray[i][1]);
			}
		}

		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.connect(FyConfig.getOA_DB_CONFIG());

			StringBuilder sb = new StringBuilder();
			sb.append("select v.科目段, v.项目编号, sum(v.预计费用), max(v.项目名称), v.公司 from zzc_all_project_view v where ");

			if (accountID != ZONGBAN_ACCOUNT_ID) {
				sb.append(" v.公司id = '").append(accountID).append("' and ");
			}
			sb.append(" v.项目编号 like '%'||?||'%'  and v.公司  like '%'||?||'%' and v.立项填写时间 >  to_date(?,'YYYY-MM-DD') and v.立项填写时间 < to_date(?,'YYYY-MM-DD') group by v.科目段, v.项目编号,v.公司");

			pst = conn.prepareStatement(sb.toString());
			pst.setString(1, number == null ? "" : number);

			pst.setString(2, companyName == null ? "" : companyName);

			pst.setString(3, MyUtil.strIsNotNull(early) ? early : "2000-01-01");
			pst.setString(4, MyUtil.strIsNotNull(last) ? last : "3000-01-01");

			rs = pst.executeQuery();

			while (rs.next()) {
				ProjectCostBean bean = new ProjectCostBean();
				bean.setSubject(rs.getString(1));
				bean.setNumber(rs.getString(2));
				bean.setYujifeiyong(String.valueOf(rs.getDouble(3)));
				bean.setProjectName(rs.getString(4));
				bean.setCompany(companyMap.get(rs.getString(5)));
				costMap.put(bean.getSubject(), bean);
				addProjectCostBean(bean);
			}

			sb = null;
			int size = costMapKeyCompany.size();
			if (size > 0) {

				StringBuilder sql = new StringBuilder()
						.append("select v3.公司名称, v3.已发生额, v3.子目段, v3.公司,v4.本月余额,v4.当月发生额,v5.上个月发生额,v6.上上个月发生额  from ( select 公司名称, sum(发生额) 已发生额, 子目段, 公司   from zzc_erp_cost_view where ");
				boolean first = true;
				sb = new StringBuilder();
				// int size1 = costMapKeyCompany.keySet().size();
				for (Iterator<String> iter = costMapKeyCompany.keySet()
						.iterator(); iter.hasNext();) {
					if (first) {
						first = false;
					} else {
						sb.append(" or ");
					}
					sb.append(" 公司=? and 子目段 in (");
					String company = iter.next();
					params.add(company);
					List<ProjectCostBean> beans = costMapKeyCompany
							.get(company);

					for (int i = 0, bSize = beans.size(); i < bSize; i++) {
						if (i != 0) {
							sb.append(",");
						}
						sb.append("?");
						params.add(beans.get(i).getSubject());
					}

					sb.append(") ");
				}

				sql.append(sb);

				sql.append(" group by 子目段, 公司,公司名称  order by 公司, 子目段, 已发生额) v3 left join (select sum(发生额) 当月发生额, 子目段, 公司, sum(本月余额) 本月余额  from zzc_erp_cost_view where ( ");

				sql.append(sb);

				sql.append(") and 期段 = to_char(sysdate, 'mm-yyyy')  group by 公司, 子目段 ) v4 on  v3.公司 = v4.公司 and v3.子目段 = v4.子目段 ");

				sql.append("  left join (select sum(发生额) 上个月发生额, 子目段, 公司 from zzc_erp_cost_view where 期段 = to_char(add_months(trunc(sysdate),-1), 'mm-yyyy') and ( ");
				sql.append(sb);
				sql.append(") group by 公司, 子目段) v5 on v3.公司 = v5.公司 and v3.子目段 = v5.子目段 left join (select sum(发生额) 上上个月发生额, 子目段, 公司 from zzc_erp_cost_view where 期段 = to_char(add_months(trunc(sysdate),-2), 'mm-yyyy') and ( ");
				sql.append(sb);
				sql.append(") group by 公司, 子目段) v6 on v3.公司 = v6.公司 and v3.子目段 = v6.子目段 order by v3.子目段, v3.公司 desc ");

				// writeLog();
				// printSQL();
				log.info(sql);
				System.out.println(sql);
				pst = conn.prepareStatement(sql.toString());

				for (int i = 0, psize = params.size(); i < psize; i++) {
					pst.setString(i + 1, params.get(i));
					pst.setString(psize + i + 1, params.get(i));
					pst.setString(2 * psize + i + 1, params.get(i));
					pst.setString(3 * psize + i + 1, params.get(i));
				}
				params = null;
				costMapKeyCompany = null;
				rs = pst.executeQuery();

				if (projectCostBeans == null) {
					projectCostBeans = new ArrayList<ProjectCostBean>();
				}

				while (rs.next()) {
					boolean exist = false;
					ProjectCostBean bean = null;
					ProjectCostBean temp = costMap.get(rs.getString(3));
					for (int j = 0, pSize = projectCostBeans.size(); j < pSize; j++) {
						bean = projectCostBeans.get(j);

						if (bean.getNumber().equals(temp.getNumber())) {
							exist = true;
							break;
						}
						bean = null;
					}
					if (!exist) {

//						if (temp.getNumber().equals("FQQC-PME-2013-121")) {
//							String d = getCost(rs.getString(6));
//							String d1 = getCost(rs.getString(7));
//							String d2 = getCost(rs.getString(8));
//							String d3 = getCost(rs.getString(5));
//							String d4 = rs.getString(2);
//							d = d + d1 + d2 + d3;
//						}

						bean = new ProjectCostBean();
						// v3.公司名称, v3.已发生额, v3.子目段,
						// v3.公司,v3.本月余额,v4.当月发生额,v5.上个月发生额,v6.上上个月发生额
						bean.setCompany(rs.getString(1));
						bean.setCost(getCost(rs.getString(2)));
						bean.setNumber(temp.getNumber());
						bean.setProjectName(temp.getProjectName());
						// bean.setSubject(temp.getSubject());
						bean.setYujifeiyong(temp.getYujifeiyong());
						bean.setIndex(projectCostBeans.size() + 1);
						bean.setMonthCost(getCost(rs.getString(6)));
						bean.setLastMonthCost(getCost(rs.getString(7)));
						bean.setLastLastMonthCost(getCost(rs.getString(8)));
						bean.setBalance(getCost(rs.getString(5)));
						projectCostBeans.add(bean);

					} else {
//						if (temp.getNumber().equals("FQQC-PME-2013-121")) {
//							String d = getCost(rs.getString(6));
//							String d1 = getCost(rs.getString(7));
//							String d2 = getCost(rs.getString(8));
//							String d3 = getCost(rs.getString(5));
//							String d4 = rs.getString(2);
//							d = d + d1 + d2 + d3;
//						}
						bean.setCost(getCost(rs.getString(2), bean.getCost()));
						bean.setMonthCost(getCost(rs.getString(6),
								bean.getMonthCost()));
						bean.setLastMonthCost(getCost(rs.getString(7),
								bean.getLastMonthCost()));
						bean.setLastLastMonthCost(getCost(rs.getString(8),
								bean.getLastLastMonthCost()));
						bean.setBalance(getCost(rs.getString(5),
								bean.getBalance()));
					}
					try {
						String percentage = (new BigDecimal(bean.getCost()))
								.multiply(new BigDecimal(100))
								.divide((new BigDecimal(bean.getYujifeiyong())
										.multiply(new BigDecimal(10000))))
								.toString();
						// String percentage = String
						// .valueOf(((new
						// BigDecimal(bean.getCost()).multiply((Double
						// .valueOf(bean.getYujifeiyong()) * 10000))) * 100));

						int index = percentage.indexOf('.');
						bean.setPercentage(percentage.substring(
								0,
								index == -1 || index + 3 > percentage.length() ? percentage
										.length() : (index + 3)));
					} catch (Exception e) {
						bean.setPercentage("异常");
					}
				}
				if (!expend) {
					for (int j = 0, pSize = projectCostBeans.size(); j < pSize; j++) {
						costBeans.put(projectCostBeans.get(j).toJSONObject());
					}
				}
			}
			success = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, pst, conn);
		}

		System.out.println("finish");

		return success;
	}

	private String getCost(String cost) {
		if (!MyUtil.strIsNotNull(cost)) {
			return "0";
		} else {
			return MyUtil.strIsNotNull(cost) ? cost : "0";
		}
	}

	private String getCost(String newCost, String oldCost) {
		if (!MyUtil.strIsNotNull(newCost) && "无发生额".equals(oldCost)) {
			return "0";

		} else {
			BigDecimal d1 = null;
			if (!"无发生额".equals(newCost)) {
				d1 = new BigDecimal(MyUtil.strIsNotNull(newCost) ? newCost
						: "0");
			}
			BigDecimal d2 = null;
			if (!"无发生额".equals(oldCost)) {
				d2 = new BigDecimal(MyUtil.strIsNotNull(oldCost) ? oldCost
						: "0");
			}

			return d1.add(d2).toString();
		}
	}

	// private void printSQL() {
	// StringBuilder sql = new StringBuilder()
	// .append("select *  from ( select 公司名称, sum(发生额) 已发生额, 子目段, 公司  from zzc_erp_cost_view where ");
	// boolean first = true;
	// StringBuilder sb = new StringBuilder();
	// // int size1 = costMapKeyCompany.keySet().size();
	// for (Iterator<String> iter = costMapKeyCompany.keySet().iterator(); iter
	// .hasNext();) {
	// if (first) {
	// first = false;
	// } else {
	// sb.append(" or ");
	// }
	// String company = iter.next();
	// sb.append(" 公司='").append(company).append("' and 子目段 in (");
	// List<ProjectCostBean> beans = costMapKeyCompany.get(company);
	//
	// for (int i = 0, bSize = beans.size(); i < bSize; i++) {
	// if (i != 0) {
	// sb.append(",");
	// }
	// sb.append("'").append(beans.get(i).getSubject()).append("'");
	// }
	//
	// sb.append(") ");
	// }
	//
	// sql.append(sb);
	//
	// sql.append(" group by 子目段, 公司,公司名称 order by 公司, 子目段, 已发生额) v3 left join (select sum(发生额) 当月发生额, 子目段, 公司  from zzc_erp_cost_view where ( ");
	//
	// sql.append(sb);
	//
	// sql.append(") and 期段 = to_char(sysdate, 'mm-yyyy')  group by 公司, 子目段 ) v4 on  v3.公司 = v4.公司 and v3.子目段 = v4.子目段 order by v3.子目段, v3.公司 desc");
	//
	// }

	/**
	 * 查询存储在ERP系统中的费用信息，科目段和公司结合
	 * 
	 * @return
	 */
	// private boolean queryCostInfo() {
	// boolean success = false;
	//
	// Connection conn = null;
	// PreparedStatement pst = null;
	// ResultSet rs = null;
	//
	// } catch (Exception e) {
	// e.printStackTrace();
	// log.error("查询项目立项费用报表异常", e);
	// } finally {
	// DBUtil.close(rs, pst, conn);
	// params = null;
	// costMapKeyCompany = null;
	// costMap = null;
	// }
	// }
	// return success;
	// }

	// public static void main(String[] args) {
	// StringBuilder sql = new StringBuilder()
	// .append("select *  from ( select 公司名称, sum(发生额) 已发生额, 子目段, 公司,本月余额  from zzc_erp_cost_view where ");
	// boolean first = true;
	// StringBuilder sb = new StringBuilder();
	// Map<String, List<ProjectCostBean>> costMapKeyCompany = new
	// HashMap<String, List<ProjectCostBean>>();
	//
	// // int size1 = costMapKeyCompany.keySet().size();
	// for (Iterator<String> iter = costMapKeyCompany.keySet().iterator(); iter
	// .hasNext();) {
	// if (first) {
	// first = false;
	// } else {
	// sb.append(" or ");
	// }
	// sb.append(" 公司=? and 子目段 in (");
	// String company = iter.next();
	// List<ProjectCostBean> beans = costMapKeyCompany.get(company);
	//
	// for (int i = 0, bSize = beans.size(); i < bSize; i++) {
	// if (i != 0) {
	// sb.append(",");
	// }
	// sb.append("?");
	// }
	//
	// sb.append(") ");
	// }
	//
	// sql.append(sb);
	//
	// sql.append(" group by 子目段, 公司,公司名称,本月余额 order by 公司, 子目段, 已发生额) v3 left join (select sum(发生额) 当月发生额, 子目段, 公司  from zzc_erp_cost_view where ( ");
	//
	// sql.append(sb);
	//
	// sql.append(") and 期段 = to_char(sysdate, 'mm-yyyy')  group by 公司, 子目段 ) v4 on  v3.公司 = v4.公司 and v3.子目段 = v4.子目段 ");
	//
	// sql.append("  left join (select sum(发生额) 上个月发生额, 子目段, 公司 from zzc_erp_cost_view where 期段 = to_char(add_months(trunc(sysdate),-1), 'mm-yyyy') and ( ");
	// sql.append(sb);
	// sql.append(") group by 公司, 子目段) v5 on v3.公司 = v5.公司 and v3.子目段 = v5.子目段 left join (select sum(发生额) 上上个月发生额, 子目段, 公司 from zzc_erp_cost_view where 期段 = to_char(add_months(trunc(sysdate),-2), 'mm-yyyy') and ( ");
	// sql.append(sb);
	// sql.append(") group by 公司, 子目段) v6 on v3.公司 = v6.公司 and v3.子目段 = v6.子目段 order by v3.子目段, v3.公司 desc ");
	// System.out.println(sql.toString());
	// }

}
